﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using DTO;

namespace DAO
{
    public class CTPhieuNhapDAO
    {
        public List<CTPhieuNhapDTO> LayCTPhieuNhap(int maPN)
        {
            string sql = " SELECT MaPN,MaSP,NgayNhap,SoLuong FROM CTPhieuNhap WHERE MaPN = " + maPN;
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            List<CTPhieuNhapDTO> danhsach = new List<CTPhieuNhapDTO>();
            OleDbDataReader reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                CTPhieuNhapDTO ct = new CTPhieuNhapDTO();
                ct.MaPN = reader.GetInt32(0);
                ct.MaSP = reader.GetInt32(1);
                ct.NgayNhap = reader.GetDateTime(2);
                ct.SoLuong = reader.GetInt32(3);
                danhsach.Add(ct);
            }
            conn.Close();
            return danhsach;
        }

        public List<CTPhieuNhapDTO> LayChiTiet(int maPN, int maSP)
        {
            string sql = "SELECT MaPN,MaSP,NgayNhap,SoLuong FROM CTPhieuNhap WHERE MaPN = " +  maPN + " AND MaSP = " + maSP;
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
     
            OleDbDataReader reader = cmd.ExecuteReader();
            List<CTPhieuNhapDTO> danhsach = new List<CTPhieuNhapDTO>();
            while (reader.Read())
            {
                CTPhieuNhapDTO ctpn = new CTPhieuNhapDTO();
                ctpn.MaPN = reader.GetInt32(0);
                ctpn.MaSP = reader.GetInt32(1);
                ctpn.NgayNhap = reader.GetDateTime(2);
                ctpn.SoLuong = reader.GetInt32(3);
                danhsach.Add(ctpn);
            }
            return danhsach;

        }

        public int ThemChiTiet(CTPhieuNhapDTO ctDTO)
        { 
            string sql = " INSERT INTO CTPhieuNhap(MaPN,MaSP,NgayNhap,SoLuong) VALUES(@MaPN,@MaSP,@NgayNhap,@SoLuong) ";
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            OleDbParameter para;
            para = cmd.Parameters.Add("@MaPN", OleDbType.Integer);
            para.Value = ctDTO.MaPN;
            para = cmd.Parameters.Add("@MaSP", OleDbType.Integer);
            para.Value = ctDTO.MaSP;
            para = cmd.Parameters.Add("@NgayNhap", OleDbType.DBDate);
            para.Value = ctDTO.NgayNhap;
            para = cmd.Parameters.Add("SoLuong", OleDbType.Integer);
            para.Value = ctDTO.SoLuong;

            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq == 1)
                return 1;
            return 0;

        }

       

        public int XoaChiTiet(int maPN)
        {
            string sql = " DELETE FROM CTPhieuNhap WHERE MaPN = " +  maPN ;
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            int kq = cmd.ExecuteNonQuery();
            conn.Close();
            if (kq == 1)
                return 1;
            return 0;
        }

        public int LaySoLuongSPDaNhap(int maPN, int maSP)
        {
            string sql = " SELECT SoLuong FROM CTPhieuNhap WHERE MaPN = " + maPN + " AND MaSP = " + maSP;
            OleDbConnection conn = DataProvider.ConnectDB();
            OleDbCommand cmd = new OleDbCommand(sql, conn);
            object obj = cmd.ExecuteScalar();
            int kq = obj == DBNull.Value ? 0 : Convert.ToInt32(obj);
            conn.Close();
            return kq;
        }

       
    }
}
